package tables;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Properties;

import beans.Utilisateur;

public class JdbcUtilisateur {

	/**
	 * 
	 * @param pseudo
	 * @return un utilisateur avec toute les informations. si l'utilisateur
	 *         n'existe pas il renvoi null
	 * @throws IOException
	 */

	public static Utilisateur findByPK(String pseudo) throws SQLException,
			ClassNotFoundException, IOException {
		Utilisateur mem = new Utilisateur();

		Properties props = new Properties();
		FileInputStream fis = new FileInputStream(
				"/home/auberi/Documents/JEE/workspace_jee/Alibi/src/connInfoLocal.txt");
		props.load(fis);

		String passwd = props.getProperty("passwd");
		String url = props.getProperty("url");
		String user = props.getProperty("user");

		// charger le driver
		Class.forName("org.postgresql.Driver");
		// se connecter à la base de données
		Connection conn = DriverManager.getConnection(url, user, passwd);
		// issue a query (create a statement and execute a query on it)
		Statement state = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
				ResultSet.CONCUR_UPDATABLE);

		String query = "SELECT * FROM utilisateur WHERE pseudo = '" + pseudo
				+ "'";
		ResultSet res = state.executeQuery(query);

		int i = 1;

		System.out.println("\n\t---------------------------------------");
		while (res.next()) {
			System.out.println("\tNom : " + res.getString("nom")
					+ " \t prénom : " + res.getString("prenom")
					+ " \t pseudo : " + res.getString("pseudo")
					+ " \t photo : " + res.getString("photo"));

			mem.setInfoG(res.getString("pseudo"), res.getString("nom"),
					res.getString("prenom"), res.getString("ville"),
					res.getString("adresse"), res.getInt("sexe"),
					res.getString("date_naissance"), // "yyyy-mm-dd"
					res.getString("orientation"), res.getString("statut"));

			mem.setInfoCompte(res.getString("mot_de_passe"));

			mem.setInfoContact(res.getString("email"),
					res.getString("telephone"));

			mem.setInfoPhy(res.getInt("taille"), res.getString("cheveux"),
					res.getString("yeux"));
			
			mem.setPhoto(res.getString("photo"));
			// On regarde si nous sommes sur la dernière ligne du résultat
			if (res.isLast()) {
				System.out.println("\t\t* DERNIER RESULTAT sur " + i + " !\n");
			}
			i++;
		}

		// Une fois terminé, nous contrôlons si nous sommes bien à l'extérieur
		// des lignes de résultat
		if (res.isAfterLast()) {
			System.out.println("about to close ResultSet\n");
		}
		res.close();
		state.close();
		return mem;
	}

	/**
	 * met a jour le mot de passe
	 * 
	 * @throws IOException
	 */
	public static void maj_mdp(String pseudo, String mdp) throws SQLException,
			ClassNotFoundException, IOException {

		Properties props = new Properties();
		FileInputStream fis = new FileInputStream(
				"/home/auberi/Documents/JEE/workspace_jee/Alibi/src/connInfoLocal.txt");
		props.load(fis);

		String passwd = props.getProperty("passwd");
		String url = props.getProperty("url");
		String user = props.getProperty("user");

		// charger le driver
		Class.forName("org.postgresql.Driver");
		// se connecter à la base de données
		Connection conn = DriverManager.getConnection(url, user, passwd);
		Statement state = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
				ResultSet.CONCUR_UPDATABLE);
		Statement state2 = conn.createStatement(
				ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
		// reccuperer la ligne a modifier
		String query = "SELECT * FROM utilisateur WHERE pseudo = '" + pseudo
				+ "'";
		ResultSet res = state.executeQuery(query);
		System.out
				.println("première requette OK::::" + findByPK(pseudo).info());
		// mise a jour de la ligne
		while (res.next()) {
			System.out.println("next ::::::::::::");
			query = "UPDATE utilisateur SET mot_de_passe='" + mdp
					+ "'WHERE pseudo = '" + pseudo + "'";

			state2.executeUpdate(query);

		}
		res.close();
		state.close();
		state2.close();
	}

	/**
	 * met a jour le mot de passe
	 * 
	 * @throws ClassNotFoundException
	 * @throws IOException
	 */
	public static void maj_coordonnees(String pseu, String vi, String adr,
			String mel, String tel) throws SQLException,
			ClassNotFoundException, IOException {

		Properties props = new Properties();
		FileInputStream fis = new FileInputStream(
				"/home/auberi/Documents/JEE/workspace_jee/Alibi/src/connInfoLocal.txt");
		props.load(fis);

		String passwd = props.getProperty("passwd");
		String url = props.getProperty("url");
		String user = props.getProperty("user");

		// charger le driver
		Class.forName("org.postgresql.Driver");
		// se connecter à la base de données
		Connection conn = DriverManager.getConnection(url, user, passwd);
		Statement state = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
				ResultSet.CONCUR_UPDATABLE);
		Statement state2 = conn.createStatement(
				ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
		// reccuperer la ligne a modifier
		String query = "SELECT * FROM utilisateur WHERE pseudo = '" + pseu
				+ "'";
		ResultSet res = state.executeQuery(query);
		// mise a jour de la ligne
		while (res.next()) {
			query = "UPDATE utilisateur SET ville='" + vi + "', adresse='"
					+ adr + "', email='" + mel + "', telephone='" + tel
					+ "'WHERE pseudo = '" + pseu + "'";
			state2.executeUpdate(query);
		}
		res.close();
		state.close();
		state2.close();
	}

	/**
	 * met a jour le statut
	 * 
	 * @throws IOException
	 */
	public static void maj_statut(String pseu, String situa)
			throws SQLException, ClassNotFoundException, IOException {

		Properties props = new Properties();
		FileInputStream fis = new FileInputStream(
				"/home/auberi/Documents/JEE/workspace_jee/Alibi/src/connInfoLocal.txt");
		props.load(fis);

		String passwd = props.getProperty("passwd");
		String url = props.getProperty("url");
		String user = props.getProperty("user");

		// charger le driver
		Class.forName("org.postgresql.Driver");
		// se connecter à la base de données
		Connection conn = DriverManager.getConnection(url, user, passwd);
		Statement state = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
				ResultSet.CONCUR_UPDATABLE);
		Statement state2 = conn.createStatement(
				ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
		// reccuperer la ligne a modifier
		String query = "SELECT * FROM utilisateur WHERE pseudo = '" + pseu
				+ "'";
		ResultSet res = state.executeQuery(query);
		// mise a jour de la ligne
		while (res.next()) {
			query = "UPDATE utilisateur SET statut='" + situa
					+ "'WHERE pseudo = '" + pseu + "'";
			state2.executeUpdate(query);
		}
		res.close();
		state.close();
		state2.close();
	}

	/**
	 * met a jour l'orientation
	 * 
	 * @throws IOException
	 */
	public static void maj_orientation(String pseu, String orientation)
			throws SQLException, ClassNotFoundException, IOException {

		Properties props = new Properties();
		FileInputStream fis = new FileInputStream(
				"/home/auberi/Documents/JEE/workspace_jee/Alibi/src/connInfoLocal.txt");
		props.load(fis);

		String passwd = props.getProperty("passwd");
		String url = props.getProperty("url");
		String user = props.getProperty("user");

		// charger le driver
		Class.forName("org.postgresql.Driver");
		// se connecter à la base de données
		Connection conn = DriverManager.getConnection(url, user, passwd);
		Statement state = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
				ResultSet.CONCUR_UPDATABLE);
		Statement state2 = conn.createStatement(
				ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
		// reccuperer la ligne a modifier
		String query = "SELECT * FROM utilisateur WHERE pseudo = '" + pseu
				+ "';";
		ResultSet res = state.executeQuery(query);
		// mise a jour de la ligne
		while (res.next()) {
			query = "UPDATE utilisateur SET orientation='" + orientation
					+ "'WHERE pseudo = '" + pseu + "'";
			state2.executeUpdate(query);
		}
		res.close();
		state.close();
		state2.close();
	}

	/**
	 * verifier si un pseudo est libre
	 * 
	 * @throws IOException
	 * @throws c
	 */
	public static boolean pseudoLibre(String pseudo) throws SQLException,
			ClassNotFoundException, IOException {
		int cpt = 0;

		Properties props = new Properties();
		FileInputStream fis = new FileInputStream(
				"/home/auberi/Documents/JEE/workspace_jee/Alibi/src/connInfoLocal.txt");
		props.load(fis);

		String passwd = props.getProperty("passwd");
		String url = props.getProperty("url");
		String user = props.getProperty("user");

		// charger le driver
		Class.forName("org.postgresql.Driver");
		// se connecter à la base de données
		Connection conn = DriverManager.getConnection(url, user, passwd);
		// issue a query (create a statement and execute a query on it)
		Statement state = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
				ResultSet.CONCUR_UPDATABLE);

		String query = "SELECT * FROM utilisateur WHERE pseudo = '" + pseudo
				+ "'";
		ResultSet res = state.executeQuery(query);
		while (res.next())
			cpt++;

		res.close();
		state.close();
		return cpt == 0;
	}

	/**
	 * ajoute un membre dans la base de donnée a partir de toute les
	 * informations sauf la photo.
	 * 
	 * @throws IOException
	 * 
	 */
	public static void addMembre(String pseudo, String nom, String prenom,
			String ville, String adresse, int sexe, String d, String os,
			String situation, String mdp, String mail, String tel,
			String photo, int taille, String cheveux, String yeux)
			throws SQLException, ClassNotFoundException, IOException {

		Properties props = new Properties();
		FileInputStream fis = new FileInputStream(
				"/home/auberi/Documents/JEE/workspace_jee/Alibi/src/connInfoLocal.txt");
		props.load(fis);

		String passwd = props.getProperty("passwd");
		String url = props.getProperty("url");
		String user = props.getProperty("user");

		// charger le driver
		Class.forName("org.postgresql.Driver");
		// se connecter à la base de données
		Connection conn = DriverManager.getConnection(url, user, passwd);
		// issue a query (create a statement and execute a query on it)
		Statement stmt = conn.createStatement();

		stmt.executeUpdate("INSERT INTO utilisateur VALUES ('" + nom + "','"
				+ prenom + "','" + pseudo + "','" + ville + "','" + adresse
				+ "','" + d + "','" + sexe + "','" + os + "','" + situation
				+ "','" + mdp + "','" + mail + "','" + tel + "','"
				+ "image/chut4.jpg" + "','" + taille + "','" + cheveux + "','"
				+ yeux + "')");

		stmt.close();
		System.out.println("membre trouvé ::: \n" + findByPK(pseudo).info());

	}

	/**
	 * supprime un membre
	 * 
	 * @param pseudo
	 * @param groupe
	 * @return 0 si ok bien suprimé, 1 si n'existe pas.
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 * @throws IOException
	 */
	public static void suppMembre(String pseudo) throws SQLException,
			ClassNotFoundException, IOException {
		// supprimer dans la BD utilisateur

		Properties props = new Properties();
		FileInputStream fis = new FileInputStream(
				"/home/auberi/Documents/JEE/workspace_jee/Alibi/src/connInfoLocal.txt");
		props.load(fis);

		String passwd = props.getProperty("passwd");
		String url = props.getProperty("url");
		String user = props.getProperty("user");

		// charger le driver
		Class.forName("org.postgresql.Driver");
		// se connecter à la base de données
		Connection conn = DriverManager.getConnection(url, user, passwd);
		// issue a query (create a statement and execute a query on it)
		Statement stmt = conn.createStatement();
		stmt.executeUpdate("DELETE FROM utilisateur WHERE pseudo = '" + pseudo
				+ "'");
		stmt.close();
		// supprimer dans les groupes
		JdbcGroupe.supallgrp(pseudo);

	}

	/**
	 * methode de mise a jour de la couleur de cheveux
	 * 
	 * @param chvx
	 * @throws IOException
	 */
	public static void maj_ch(String pseu, String chvx) throws SQLException,
			ClassNotFoundException, IOException {

		Properties props = new Properties();
		FileInputStream fis = new FileInputStream(
				"/home/auberi/Documents/JEE/workspace_jee/Alibi/src/connInfoLocal.txt");
		props.load(fis);

		String passwd = props.getProperty("passwd");
		String url = props.getProperty("url");
		String user = props.getProperty("user");

		// charger le driver
		Class.forName("org.postgresql.Driver");
		// se connecter à la base de données
		Connection conn = DriverManager.getConnection(url, user, passwd);
		// issue a query (create a statement and execute a query on it)
		Statement state = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
				ResultSet.CONCUR_UPDATABLE);
		Statement state2 = conn.createStatement(
				ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
		// reccuperer la ligne a modifier
		String query = "SELECT * FROM utilisateur WHERE pseudo = '" + pseu
				+ "'";
		ResultSet res = state.executeQuery(query);
		// mise a jour de la ligne
		while (res.next()) {
			query = "UPDATE utilisateur SET cheveux='" + chvx
					+ "'WHERE pseudo = '" + pseu + "'";
			state2.executeUpdate(query);
		}
		res.close();
		state.close();
		state2.close();
	}

	/**
	 * methode de mise a jour la photo de membre
	 * 
	 * @param photo
	 * @throws IOException
	 */
	public static void maj_photo(String pseu, String photo)
			throws SQLException, ClassNotFoundException, IOException {

		Properties props = new Properties();
		FileInputStream fis = new FileInputStream(
				"/home/auberi/Documents/JEE/workspace_jee/Alibi/src/connInfoLocal.txt");
		props.load(fis);

		String passwd = props.getProperty("passwd");
		String url = props.getProperty("url");
		String user = props.getProperty("user");

		// charger le driver
		Class.forName("org.postgresql.Driver");
		// se connecter à la base de données
		Connection conn = DriverManager.getConnection(url, user, passwd);
		// issue a query (create a statement and execute a query on it)
		Statement state = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
				ResultSet.CONCUR_UPDATABLE);
		Statement state2 = conn.createStatement(
				ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
		// reccuperer la ligne a modifier
		String query = "SELECT * FROM utilisateur WHERE pseudo = '" + pseu
				+ "'";
		ResultSet res = state.executeQuery(query);

		// mise a jour de la ligne
		while (res.next()) {
			System.out.println("dans JDBC Ut : step");
			query = "UPDATE utilisateur SET photo='" + photo
					+ "'WHERE pseudo = '" + pseu + "'";
			System.out.println(query);
			state2.executeUpdate(query);
		}
		res.close();
		state.close();
		state2.close();
	}

	/**
	 * methode pour connaitre le nombre de membre
	 * 
	 * @throws IOException
	 */

	public static int nbMembre() throws SQLException, ClassNotFoundException,
			IOException {
		int nombreLignes = 0;

		Properties props = new Properties();
		FileInputStream fis = new FileInputStream(
				"/home/auberi/Documents/JEE/workspace_jee/Alibi/src/connInfoLocal.txt");
		props.load(fis);

		String passwd = props.getProperty("passwd");
		String url = props.getProperty("url");
		String user = props.getProperty("user");

		// charger le driver
		Class.forName("org.postgresql.Driver");
		// se connecter à la base de données
		Connection conn = DriverManager.getConnection(url, user, passwd);
		// issue a query (create a statement and execute a query on it)
		Statement state = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
				ResultSet.CONCUR_UPDATABLE);
		// Statement state2 = conn.createStatement(
		// ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
		// reccuperer la ligne a modifier
		String query = "SELECT * FROM utilisateur ;";
		ResultSet resultat = state.executeQuery(query);
		// on place le curseur sur le dernier tuple
		resultat.last();
		// on récupère le numéro de la ligne
		nombreLignes = resultat.getRow();
		// on repace le curseur avant la première ligne
		resultat.beforeFirst();

		resultat.close();
		state.close();
		// state2.close();
		return nombreLignes;
	}
	/**
	 * donne la lsite des membres du site
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 * @throws IOException
	 */
	public static ArrayList<Utilisateur> getMembres() throws SQLException, ClassNotFoundException, IOException{
		ArrayList<Utilisateur> membres=new ArrayList<Utilisateur>();
		Properties props = new Properties();
		FileInputStream fis = new FileInputStream(
				"/home/auberi/Documents/JEE/workspace_jee/Alibi/src/connInfoLocal.txt");
		props.load(fis);

		String passwd = props.getProperty("passwd");
		String url = props.getProperty("url");
		String user = props.getProperty("user");

		// charger le driver
		Class.forName("org.postgresql.Driver");
		// se connecter à la base de données
		Connection conn = DriverManager.getConnection(url, user, passwd);
		// issue a query (create a statement and execute a query on it)
		Statement state = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
				ResultSet.CONCUR_UPDATABLE);
		// Statement state2 = conn.createStatement(
		// ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
		// reccuperer la ligne a modifier
		String query = "SELECT pseudo FROM utilisateur ;";
		ResultSet resultat = state.executeQuery(query);
		// on place le curseur sur le dernier tuple
		while(resultat.next()){
			membres.add(findByPK(resultat.getString("pseudo")));
		}
		
		resultat.close();
		state.close();
		return membres;
	}
	

}
